/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package Model;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

/**
 *
 * @author 013v
 */
public class CustomerManager {

    public boolean addSecUser(UserSecure newSec) {
        boolean result = false;

        try {
            DBConnection db = new DBConnection();
            Connection conn = db.getConnection();

            int i = 1;

            String que = "INSERT into usersecure(securityQuestion, securityAnswer, AccountID) VALUES(?,?,?)";
            PreparedStatement pstmt =
                    conn.prepareStatement(que);

            pstmt.setString(i++, newSec.getSecurityQuestion());
            pstmt.setString(i++, newSec.getSecurityAnswer());
            pstmt.setString(i++, newSec.getAccountID());

            pstmt.executeUpdate();
            //close DB connection
            conn.close();

            result = true;
        } catch (SQLException ex) {
            ex.printStackTrace();
        }

        return result;
    }

    public boolean addCustomer(Customer newCust) {
        boolean result = false;
        int newID = new RandomGenerator().generateFakeAccountID();

        try {
            boolean res1 = new UserManager().addUser(newCust.getNewUser());
            boolean res2 = new DeliveryAddressManager().addDelAddress(newCust.getNewDeliv());
            boolean res3 = new CreditCardManager().addCreditCard(newCust.getNewCred());
            boolean res4 = new CustomerManager().addSecUser(newCust.getNewSec());
            boolean res5 = new UserManager().addUserIDTrack(newCust.getNewUser(), newID);

            if (res1 == true && res2 == true && res3 == true && res4 == true && res5 == true) {
                result = true;
            }

        } catch (Exception e) {
            e.printStackTrace();
        }

        return result;
    }

    public Customer getCustomerDetails(String user) {
        Customer result = null;
        try {
            User use = new UserManager().getUser(user);
            CreditCard cc = new CreditCardManager().getCard(user);
            DeliveryAddress da = new DeliveryAddressManager().getDel(user);

            if (use != null && cc != null && da != null) {
                result = new Customer();
                result.setNewUser(use);
                result.setNewCred(cc);
                result.setNewDeliv(da);
            }

        } catch (Exception e) {
            e.printStackTrace();
        }
        return result;
    }

    public boolean checkifFilled(String[][] cart) {
        boolean result = false;
        try {
            int index = cart[0].length;
            int i = 0;
            while (i < index) {
                if (cart[0][i] != null) {
                    result = true;
                }
                i++;
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return result;
    }

    public int getNumInCart(String[][] cart) {
        int result = 0;
        try {
            int index = cart[0].length;
            for (int i = 0; i < index; i++) {
                if (cart[0][i] != null) {
                    result++;
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return result;
    }

    public boolean ProductCheckOut(Transaction newTrans, String[][] cart) { //to be continued  Dummy
        boolean result = false;
        int newFID = new RandomGenerator().generateFakeTransactionID();

        try {
            DBConnection db = new DBConnection();
            Connection conn = db.getConnection();


            String que = "INSERT into transaction(TransactionID, TransactionTimeStamp, TransactionTotal, AccountID) VALUES(?,NOW(),?,?)";
            PreparedStatement pstmt =
                    conn.prepareStatement(que);

            pstmt.setInt(1, newTrans.getTransactionID());
            pstmt.setInt(2, newTrans.getTransactionTotal());
            pstmt.setString(3, newTrans.getAccountID());

            pstmt.executeUpdate(); //add to transaction table

            String que1 = "INSERT into transidtrack(TransactionID, FakeTransactionID) VALUES(?,?)";
            PreparedStatement pstmt1 =
                    conn.prepareStatement(que1);

            pstmt1.setInt(1, newTrans.getTransactionID());
            pstmt1.setInt(2, newFID);

            pstmt1.executeUpdate(); //generate fake id

            String que2 = "INSERT into purchaseditems(ProductID, Quantity, PricePerQuantity, TransactionID) VALUES(?,?,?,?)";
            PreparedStatement pstmt2 =
                    conn.prepareStatement(que2);

            int index = cart[0].length;
            int x = 0;
            while (x < index) {
                if (cart[0][x] != null) {
                    pstmt2.setInt(1, new ProductManager().getOProdID(cart[0][x]));
                    pstmt2.setString(2, cart[1][x]);
                    pstmt2.setString(3, cart[2][x]);
                    pstmt2.setInt(4, newTrans.getTransactionID());
                    pstmt2.executeUpdate(); //add cart content to purchased items
                }
                x++;
            }

            conn.close();

            result = true;
        } catch (SQLException ex) {
            ex.printStackTrace();
        }

        return result;
    }

    public ResultSet getCredDel(String uid) {
        String id = new UserManager().getAccountIDFromUser(uid);
        try {
            DBConnection db = new DBConnection();
            Connection conn = db.getConnection();

            String que = "SELECT CONCAT(SUBSTRING(c.CCCardNum,1,4), '-****-****-****-') as 'CredNum', "
                    + "CONCAT(d.DAHouseNum, ' ', d.DAStreet, ' ', d.DASubdivision, ' ', d.DACity, ' ', d.DAZip, ' ',"
                    + " d.DACountry) as 'DeliveryAdd' FROM creditcardinfo c, deliveryaddressinfo d "
                    + "WHERE c.AccountID = d.AccountID and c.AccountID = ?";

            PreparedStatement pstmt =
                    conn.prepareStatement(que);

            pstmt.setString(1, id);
            ResultSet res = pstmt.executeQuery();
            return res;

        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }
    }

    public Transaction getTrans(String uid) {
        String id = new UserManager().getAccountIDFromUser(uid);
        Transaction result = null;
        try {
            DBConnection db = new DBConnection();
            Connection conn = db.getConnection();

            String que = "SELECT ft.FakeTransactionID as 'TransactionID', "
                    + "DATE_FORMAT(t.TransactionTimeStamp, '%b %e %Y %r')as 'Date', "
                    + "t.TransactionTotal FROM transaction t join transidtrack ft "
                    + "on ft.TransactionID = t.TransactionID WHERE t.AccountID = ? ORDER BY 2 desc limit 1";

            PreparedStatement pstmt =
                    conn.prepareStatement(que);

            pstmt.setString(1, id);
            ResultSet rst = pstmt.executeQuery();

            result = new Transaction();
            while (rst.next()) {
                result.setTransactionID(rst.getInt("TransactionID"));
                result.setTransactionTimeStamp(rst.getString("Date"));
                result.setTransactionTotal(rst.getInt("TransactionTotal"));
            }

            conn.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return result;
    }

    public int getOTransID(String TID) {
        int result = 0;
        try {
            DBConnection db = new DBConnection();
            Connection conn = db.getConnection();

            String que = "SELECT TransactionID FROM transidtrack where FakeTransactionID = ?";
            PreparedStatement pstat = conn.prepareStatement(que);
            pstat.setString(1, TID);

            ResultSet rst = pstat.executeQuery();

            if (rst.next()) {
                result = rst.getInt("TransactionID");
            }

            conn.close();

        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        return result;
    }

    public ResultSet getPurchaseItems(String tid) {
        int id = getOTransID(tid);

        try {
            DBConnection db = new DBConnection();
            Connection conn = db.getConnection();

            String que = "SELECT p.ProductTitle 'ProdTitle', pi.Quantity as 'Quantity', pi.PricePerQuantity as 'Price' "
                    + "FROM product p, purchaseditems pi WHERE p.ProductID = pi.ProductID "
                    + "and pi.TransactionID = ?;";

            PreparedStatement pstmt =
                    conn.prepareStatement(que);

            pstmt.setInt(1, id);

            ResultSet rst = pstmt.executeQuery();

            return rst;
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }

    }

    public int getStock(String FID) {
        int result = 0;
        int pid = new ProductManager().getOProdID(FID);

        try {
            DBConnection db = new DBConnection();
            Connection conn = db.getConnection();

            String getStock = "SELECT ProductQuantity FROM product WHERE ProductID = ?";
            PreparedStatement pstmt = conn.prepareStatement(getStock);

            pstmt.setInt(1, pid);
            ResultSet rst = pstmt.executeQuery();

            if (rst.next()) {
                result = rst.getInt("ProductQuantity");
            }

            conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return result;
    }

    public boolean decreaseStock(Product newProd, String quantity) {
        boolean result = false;
        int tempStock = newProd.getProductQuantity() - Integer.parseInt(quantity);
        int pid = new ProductManager().getOProdID(Integer.toString(newProd.getProductID()));

        try {
            DBConnection db = new DBConnection();
            Connection conn = db.getConnection();

            String decStock = "UPDATE product SET ProductQuantity = ? WHERE ProductID = ?";
            PreparedStatement pstat = conn.prepareStatement(decStock);
           
            pstat.setInt(1, tempStock);
            pstat.setInt(2, pid);

            pstat.executeUpdate();

            result = true;
            
            conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return result;
    }
    
    public boolean increaseStock(Product newProd, String quantity) {
        boolean result = false;
        int tempStock = newProd.getProductQuantity() + Integer.parseInt(quantity);
        int pid = new ProductManager().getOProdID(Integer.toString(newProd.getProductID()));

        try {
            DBConnection db = new DBConnection();
            Connection conn = db.getConnection();

            String incStock = "UPDATE product SET ProductQuantity = ? WHERE ProductID = ?";
            PreparedStatement pstat = conn.prepareStatement(incStock);
           
            pstat.setInt(1, tempStock);
            pstat.setInt(2, pid);

            pstat.executeUpdate();

            result = true;
            
            conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return result;
    }
}
